package jp.ac.u_tokyo.dch.dao.user;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.xwork.StringUtils;


import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.User;
import jp.ac.u_tokyo.dch.util.PasswordUtil;
import jp.ac.u_tokyo.dch.util.common.CommonUtil;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;
/**
* @version 1.1
*/
public class UsersDao {


	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 *
	 * @param DBコネクションオブジェクト
	 */
	public UsersDao(DBConnection dbconn) {
		this.dbconn = dbconn;
	}

	/**
	 * 検索する
	 * @param userid
	 * @param userName
	 * @param groupId
	 * @param groupName
	 * @return
	 * @throws DBException
	 */
	public List<User> searchUsers(String userid, String userName, String groupId, String groupName) throws DBException {

		List<User> userList = new ArrayList<User>();

		StringBuilder sqlQuery = new StringBuilder();

		List<String> whereList = new ArrayList<String>();

		sqlQuery.append("SELECT * FROM users ");

		if (StringUtils.isNotEmpty(userid) || StringUtils.isNotEmpty( userName)) {

			whereList.add(" EXISTS  (SELECT * FROM users as users2 WHERE id like ? AND name like ? AND users.id = users2.id) ");
		}

		if (StringUtils.isNotEmpty(groupId) || StringUtils.isNotEmpty( groupName)) {

			whereList.add(" EXISTS  (SELECT * FROM user_group_info INNER JOIN user_group on user_group_info.groupid = user_group.id where user_group.id like ? AND user_group. name like ? and users.id = user_group_info.userid) ");
		}

		if (!whereList.isEmpty()) {

			sqlQuery.append("WHERE ");
			sqlQuery.append(StringUtils.join(whereList, "AND"));
		}


		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {

			ps = dbconn.prepareStatement(sqlQuery.toString());
			int i = 1;

			if (StringUtils.isNotEmpty(userid) || StringUtils.isNotEmpty( userName)) {

				if (StringUtils.isEmpty(userid)) {
					ps.setString(i++, "%");
				} else {
					ps.setString(i++, CommonUtil.escapeLike(userid));
				}
				if (StringUtils.isEmpty(userName)) {
					ps.setString(i++, "%");
				} else {
					ps.setString(i++, CommonUtil.escapeLike(userName));
				}
			}

			if (StringUtils.isNotEmpty(groupId) || StringUtils.isNotEmpty( groupName)) {

				if (StringUtils.isEmpty(groupId)) {
					ps.setString(i++, "%");
				} else {
					ps.setString(i++, CommonUtil.escapeLike(groupId));
				}
				if (StringUtils.isEmpty(groupName)) {
					ps.setString(i++, "%");
				} else {
					ps.setString(i++, CommonUtil.escapeLike(groupName));
				}

			}

			rs = ps.executeQuery();

			while (rs.next()) {

				User user = new User();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setFrontLevel(rs.getString("frontlevel"));
				user.setManageLevel(rs.getString("managelevel"));
				user.setCreateUser(rs.getString("createuser"));
				user.setCreateDate(rs.getString("createdate"));
				user.setUpdateUser(rs.getString("updateuser"));
				user.setUpdateDate(rs.getString("updatedate"));

				userList.add(user);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return userList;
	}


	/**
	 * ユーザグループリストを取得する
	 * @return ユーザグループリスト
	 * @throws DBException 例外
	 */
	public List<User> selectAll() throws DBException {

		List<User> userList = new ArrayList<User>();

		String sqlQuery = "select * from users";

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			rs = ps.executeQuery();

			while (rs.next()) {

				User user = new User();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setFrontLevel(rs.getString("frontlevel"));
				user.setManageLevel(rs.getString("managelevel"));
				user.setCreateUser(rs.getString("createuser"));
				user.setCreateDate(rs.getString("createdate"));
				user.setUpdateUser(rs.getString("updateuser"));
				user.setUpdateDate(rs.getString("updatedate"));

				userList.add(user);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return userList;
	}

	public User selectByIdPassWord(String id, String pass) throws DBException {

		User user = null;

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * from users where id = ? and password = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());

			ps.setString(1, id);
			ps.setString(2, PasswordUtil.getHashPassword(pass));

			rs = ps.executeQuery();

			if (rs.next()) {

				user = new User();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setFrontLevel(rs.getString("frontlevel"));
				user.setManageLevel(rs.getString("managelevel"));
				user.setCreateUser(rs.getString("createuser"));
				user.setCreateDate(rs.getString("createdate"));
				user.setUpdateUser(rs.getString("updateuser"));
				user.setUpdateDate(rs.getString("updatedate"));

			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return user;
	}

	public User selectById(String id) throws DBException {

		User user = null;

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * from users where id = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());

			ps.setString(1, id);

			rs = ps.executeQuery();

			if (rs.next()) {

				user = new User();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setFrontLevel(rs.getString("frontlevel"));
				user.setManageLevel(rs.getString("managelevel"));
				user.setCreateUser(rs.getString("createuser"));
				user.setCreateDate(rs.getString("createdate"));
				user.setUpdateUser(rs.getString("updateuser"));
				user.setUpdateDate(rs.getString("updatedate"));

			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return user;
	}

	/**
	 * IDグループをチェックする
	 * @param id ユーザID
	 * @return true : 存在する、false：存在しない
	 * @throws Exception 例外
	 */
	public boolean checkId(String id)throws DBException{

		boolean isExists = false;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		String sql = "select * from users where id = ?";
		try{
			ps = dbconn.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			isExists = rs.next();
		}catch(DBException e) {
			throw e;
		} finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return isExists;
	}

	/**
	 * 更新する
	 * @param userGroup ユーザグループ
	 * @return 更新件数
	 * @throws DBException 例外
	 */
	public int update(User user) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "UPDATE users SET name = ?, password = ?, frontlevel = ?, managelevel = ?, updateuser = ?, updatedate = sysdate() WHERE id = ?";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++, user.getName());
			ps.setString(index++, PasswordUtil.getHashPassword(user.getPassword()));
			ps.setString(index++, user.getFrontLevel());
			ps.setString(index++, user.getManageLevel());
			ps.setString(index++, user.getUpdateUser());
			ps.setString(index++, user.getId());
			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}
		return reslut;
	}

	/**
	 * 削除する
	 * @param userGroup ユーザグループ
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(User user) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "DELETE FROM users WHERE id = ?";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++, user.getId());
			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}

		return reslut;
	}

	/**
	 * 挿入する
	 * @param userGroup ユーザグループ
	 * @throws DBException 例外
	 */
	public int insert(User user) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "INSERT INTO users (id,name,password,frontlevel,managelevel,createuser,createdate,updateuser,updatedate)  VALUES (?,?,?,?,?,?,sysdate(),?,sysdate())";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++, user.getId());
			ps.setString(index++, user.getName());
			ps.setString(index++, PasswordUtil.getHashPassword(user.getPassword()));
			ps.setString(index++, user.getFrontLevel());
			ps.setString(index++, user.getManageLevel());
			ps.setString(index++, user.getCreateUser());
			ps.setString(index++, user.getUpdateUser());
			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}

		return reslut;
	}
}
